/**********************************************************************************
 * Copyright (c) 2012 - 2012,  Collect4PNP, All right reserved
 * Title: com.cipas.pnp.jdbc  DBUtil.java 
 * @author shen  shenqiao2012@163.com 
 *  2012-7-11 上午11:52:44 
 *********************************************************************************/


package com.adc.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.adc.jdbc.pool.PoolHandle;



public class DBUtil {
	
	/**
	 * 
	 * Description: 根据指定的连接池名称、查询sql，查询数据库，返回记录序列；
	 * author:  shen
	 * @param poolName 连接池名称
	 * @param sql  查询sql
	 * @return List<List<String> >
	 */
	public static List<List<String> > getResult(String poolName,String sql) {
		List<List<String> > result=new ArrayList<List<String>>();
		List<String> record=new ArrayList<String>();
		Connection con=null;
		Statement stmt=null;
		ResultSet resultSet=null;
		con=PoolHandle.getInstance().getConnection(poolName);
		if (con!=null) {
			System.out.println("Connection successful!");
			try {
				stmt = con.createStatement();
				resultSet= stmt.executeQuery(sql); // do something with the connection.
				int columnSize=resultSet.getMetaData().getColumnCount();
				while(resultSet.next()){
					record=new ArrayList<String>();
					for (int i = 0; i < columnSize; i++) {
						record.add(resultSet.getString(i+1));
					}  // add result to list
					result.add(record);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					resultSet.close();
					stmt.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}else {
			System.out.println("Can not get DB connection!");
		}
		return result;
	}
	
	/**
	 * 
	 * Description: 根据数据库连接池名称，对指定的库执行任意sql
	 * author:  shen
	 * @param poolName
	 * @param sql  
	 * @return boolean
	 */
	public static boolean dbDoEveryThingBySQL(String poolName,String sql) {
		boolean result=false;
		Connection con=null;
		Statement stmt=null;
		if ((con=PoolHandle.getInstance().getConnection(poolName))!=null) {
			System.out.println("Connection successful!");
			try {
				stmt = con.createStatement();
				stmt.execute(sql); // do something with the connection.
				result= true;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					stmt.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("The operation's result is :"+result);
			}
		}else {
			System.out.println("Can not get DB connection!");
		}
		return result;
	}
	
	/**
	 * 
	 * Description: 根据数据库连接池名称，对指定的库执行insert\ update \ delete 的  sql
	 * author:  shen
	 * @param poolName 数据库连接池的名称
	 * @param sql  增、删、改操作的sql
	 * @return boolean
	 */
	public static boolean dbOperaIUD(String poolName,String sql) {
		boolean result=false;
		Connection con=null;
		Statement stmt=null;
		if ((con=PoolHandle.getInstance().getConnection(poolName))!=null) {
			System.out.println("Connection successful!");
			try {
				stmt = con.createStatement();
				int line= stmt.executeUpdate(sql); // do something with the connection.
				if (line>-1) {
					result=true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					stmt.close();
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("The operation's result is :"+result);
			}
		}else {
			System.out.println("Can not get DB connection!");
		}
		return result;
	}

	/**
	 * Description: 这里用一句话描述这个方法的作用 
	 * author:  shen
	 * @param args 
	 * @return void
	 */
	public static void main(String[] args) {
		String sql="select count(*) from t_trans_monitor_info_20130109 b ";
		List<List<String> > res=getResult("testdb", sql);
		System.out.println(res.size());
		for (List<String> list : res) {
			String tempString="";
			for (String string : list) {
				tempString=tempString+","+string;
			}
			System.out.println("RES:"+tempString.substring(1));
		}
	}

}
